﻿using MiniExcelLibs;
using Sunny.UI;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace PanhuaWin.工厂.仓库.报表
{
    public partial class Form投入产出汇总 : UIPage
    {
        DataTable tbbb = new DataTable();
        public Form投入产出汇总()
        {
            InitializeComponent();
            uiDataGridView临时.AutoGenerateColumns = false;
        }

        private void uiButton查询_Click(object sender, EventArgs e)
        {
            string 查询开始 = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            this.uiLabel时间长度.Text = 查询开始;
            string guid = Guid.NewGuid().ToString();
            投入产出汇总(guid, this.uiDatePicker开始日期.Value, this.uiDatePicker结束日期.Value, "1");
            query(guid);

            string 查询结束 = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            this.uiLabel时间长度.Text = 查询开始 + "~" + 查询结束;
            MessageBox.Show("查询结束,开始时间:" + 查询开始 + ",结束时间:" + 查询结束);
        }
        private void 投入产出汇总(string guid, DateTime 开始日期, DateTime 结束日期, string 版本 = "新")
        {
            string sql;
            //1塞入酸洗的
            #region 
            sql = " insert into Temp_投入产出表(guid, 产线名称, 产出日期, 投入卷号, 产出卷号,   产出总重量  ) ";
            sql += " select '" + guid + "', 产线名称,生产日期,投入卷号,产出卷号,产出重量 from View_酸洗投入产出明细表 ";
            sql += " where 生产日期 >='" + 开始日期.ToString() + "'";

            pub.ExecuteSql(pub.ConnectionString, sql);

            sql = " update a set a.投入重量 =b.OriginalWeight from   Temp_投入产出表 a ,Bus_PicklingOriginal b ";
            sql += " where a.投入卷号=b.OriginalVolumeNew and a.guid='" + guid + "' and b.CreateDate >='" + 开始日期.ToString() + "' ";

            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = " update a set a.产出A1 =b.OutputWeight/1000 from   Temp_投入产出表 a ,Bus_PicklingOutput b ";
            sql += " where a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "' and b.OutputDate >='" + 开始日期.ToString() + "'" +
                " and b.OutputQualityGrade ='A1'";
            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = " update a set a.产出A2 =b.OutputWeight/1000 from   Temp_投入产出表 a ,Bus_PicklingOutput b ";
            sql += " where a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "' and b.OutputDate >='" + 开始日期.ToString() + "' " +
                    " and b.OutputQualityGrade ='A2'";
            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = " update a set a.产出B =b.OutputWeight/1000 from   Temp_投入产出表 a ,Bus_PicklingOutput b ";
            sql += " where a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "' and b.OutputDate >='" + 开始日期.ToString() + "'" +
                  " and b.OutputQualityGrade ='B'";
            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = " update a set a.产出s =b.OutputWeight/1000 from   Temp_投入产出表 a ,Bus_PicklingOutput b ";
            sql += " where a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "' and b.OutputDate >='" + 开始日期.ToString() + "'" +
                   " and b.OutputQualityGrade ='S'";
            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = " update a set a.汇总面积 =CONVERT(decimal(18, 0),b.OutputWidth)*CONVERT(decimal(18, 0),b.OutputMeter)/1000 from   Temp_投入产出表 a ,";
            sql += " Bus_PicklingOutput b where a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "' and b.OutputDate >='" + 开始日期.ToString() + "'";
            pub.ExecuteSql(pub.ConnectionString, sql);
            #endregion
            //2塞入轧硬的
            sql = " insert into Temp_投入产出表(guid, 产线名称, 产出日期, 投入卷号, 产出卷号,   产出总重量  ) ";
            sql += " SELECT '" + guid + "',  产线名称,生产日期, 投入卷号,产出卷号,产出重量 FROM      View_轧硬投入产出明细表  ";
            sql += " where 生产日期 >='" + 开始日期.ToString() + "'";

            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = " update a set a.投入重量 =b.OriginalWeight from   Temp_投入产出表 a ,Bus_HardOriginal b ";
            sql += "  where a.投入卷号=b.OriginalVolumeNew and a.guid='" + guid + "'  and b.CreateDate >='" + 开始日期.ToString() + "' ";

            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = " update a set a.产出A1 =b.OutputWeight/1000 from   Temp_投入产出表 a ,Bus_HardOutput b ";
            sql += "  where a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "'and b.OutputDate >='" + 开始日期.ToString() + "'" +
                " and b.OutputQualityGrade ='A1'";
            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = " update a set a.产出A2 =b.OutputWeight/1000 from   Temp_投入产出表 a ,Bus_HardOutput b ";
            sql += "  where a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "' and b.OutputDate >='" + 开始日期.ToString() + "'" +
                    " and b.OutputQualityGrade ='A2'";
            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = " update a set a.产出B =b.OutputWeight/1000 from   Temp_投入产出表 a ,Bus_HardOutput b ";
            sql += " where a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "' and b.OutputDate >='" + 开始日期.ToString() + "'" +
                  " and b.OutputQualityGrade ='B'";
            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = " update a set a.产出s =b.OutputWeight/1000 from   Temp_投入产出表 a ,Bus_HardOutput b ";
            sql += " where a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "' and b.OutputDate >='" + 开始日期.ToString() + "'" +
                   " and b.OutputQualityGrade ='S'";
            pub.ExecuteSql(pub.ConnectionString, sql);

            sql = " update a set a.汇总面积 =CONVERT(decimal(18, 0),b.OutputWidth)*CONVERT(decimal(18, 0),b.OutputMeter)/1000 from   Temp_投入产出表 a ,Bus_HardOutput b ";
            sql += " where a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "' and b.OutputDate >='" + 开始日期.ToString() + "'";
            pub.ExecuteSql(pub.ConnectionString, sql);


            //3塞入镀锌的
            sql = "insert into Temp_投入产出表(guid, 产线名称, 产出日期, 投入卷号, 产出卷号,   产出总重量  )";
            sql += "SELECT '" + guid + "',  产线名称,生产日期, 投入卷号, 产出卷号, 产出重量 FROM      View_镀锌投入产出明细表 ";
            sql += " where 生产日期 >='" + 开始日期.ToString() + "'";

            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = " update a set a.投入重量 =b.OriginalWeight/1000 from   Temp_投入产出表 a ,Bus_ZincOriginal b ";
            sql += " where a.投入卷号=b.OriginalVolumeNew and a.guid='" + guid + "'  and b.CreateDate >='" + 开始日期.ToString() + "' ";

            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = " update a set a.产出A1 =b.OutputWeight/1000 from   Temp_投入产出表 a ,Bus_ZincOutput b ";
            sql += " where a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "' and b.OutputDate >='" + 开始日期.ToString() + "'" +
                " and b.OutputQualityGrade ='A1'";
            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = " update a set a.产出A2 =b.OutputWeight/1000 from   Temp_投入产出表 a ,Bus_ZincOutput b";
            sql += " where a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "' and b.OutputDate >='" + 开始日期.ToString() + "'" +
                    " and b.OutputQualityGrade ='A2'";
            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = "update a set a.产出B =b.OutputWeight/1000 from   Temp_投入产出表 a ,Bus_ZincOutput b";
            sql += " where a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "' and b.OutputDate >='" + 开始日期.ToString() + "'" +
                  " and b.OutputQualityGrade ='B'";
            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = "update a set a.产出s =b.OutputWeight/1000 from   Temp_投入产出表 a ,Bus_ZincOutput b where";
            sql += " a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "' and b.OutputDate >='" + 开始日期.ToString() + "'" +
                   " and b.OutputQualityGrade ='S'";
            pub.ExecuteSql(pub.ConnectionString, sql);

            sql = "update a set a.汇总面积 =CONVERT(decimal(18, 0),b.OutputWidth)*CONVERT(decimal(18, 0),b.OutputMeter)/1000 from   Temp_投入产出表 a ,Bus_ZincOutput b";
            sql += " where a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "' and b.OutputDate >='" + 开始日期.ToString() + "'";
            pub.ExecuteSql(pub.ConnectionString, sql);

            //4塞入彩涂的
            sql = "insert into Temp_投入产出表(guid, 产线名称, 产出日期, 投入卷号, 产出卷号,   产出总重量  )";
            sql += "SELECT  '" + guid + "',产线名称,生产日期, 投入卷号,产出卷号, 产出重量 FROM      View_彩涂投入产出明细表 ";
            sql += " where 生产日期 >='" + 开始日期.ToString() + "'";

            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = "update a set a.投入重量 =b.OriginalWeight/1000 from   Temp_投入产出表 a ,Bus_PaintOriginal b ";
            sql += " where a.投入卷号=b.OriginalVolumeNew and a.guid='" + guid + "'  and b.CreateDate >='" + 开始日期.ToString() + "' ";

            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = "update a set a.产出A1 =b.OutputWeight/1000 from   Temp_投入产出表 a ,Bus_PaintOutput b ";
            sql += "where a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "' and b.OutputDate >='" + 开始日期.ToString() + "'" +
                " and b.OutputQualityGrade ='A1'";
            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = "update a set a.产出A2 =b.OutputWeight/1000 from   Temp_投入产出表 a ,Bus_PaintOutput b ";
            sql += "where a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "' and b.OutputDate >='" + 开始日期.ToString() + "'" +
                    " and b.OutputQualityGrade ='A2'";
            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = "update a set a.产出B =b.OutputWeight/1000 from   Temp_投入产出表 a ,Bus_PaintOutput b";
            sql += " where a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "' and b.OutputDate >='" + 开始日期.ToString() + "'" +
                  " and b.OutputQualityGrade ='B'";
            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = "update a set a.产出s =b.OutputWeight/1000 from   Temp_投入产出表 a ,Bus_PaintOutput b";
            sql += " where a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "' and b.OutputDate >='" + 开始日期.ToString() + "'" +
                   " and b.OutputQualityGrade ='S'";
            pub.ExecuteSql(pub.ConnectionString, sql);

            sql = "update a set a.汇总面积 =CONVERT(decimal(18, 0),b.OutputWidth)*CONVERT(decimal(18, 0),b.OutputMeter)/1000 from   Temp_投入产出表 a ,Bus_PaintOutput b ";
            sql += "where a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "' and b.OutputDate >='" + 开始日期.ToString() + "'";
            pub.ExecuteSql(pub.ConnectionString, sql);
            //5塞入硅钢的
            sql = "insert into Temp_投入产出表(guid, 产线名称, 产出日期, 投入卷号, 产出卷号,   产出总重量  )";
            sql += @"SELECT   '" + guid + "',产线名称,CreateDate, 投入卷号,产出卷号,产出重量 from  View_硅钢投入产出明细表 ";
            sql += " where 生产日期 >='" + 开始日期.ToString() + "'";

            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = "update a set a.投入重量 =b.OriginalWeight/1000 from   Temp_投入产出表 a ,Bus_SiOriginal b ";
            sql += "where a.投入卷号=b.OriginalVolumeNew and a.guid='" + guid + "'  and b.CreateDate >='" + 开始日期.ToString() + "' ";

            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = "update a set a.产出A1 =b.OutputWeight/1000 from   Temp_投入产出表 a ,Bus_SiOutput b ";
            sql += "where a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "' and b.OutputDate >='" + 开始日期.ToString() + "'" +
                " and b.OutputQualityGrade ='A1'";
            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = "update a set a.产出A2 =b.OutputWeight/1000 from   Temp_投入产出表 a ,Bus_SiOutput b ";
            sql += "where a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "' and b.OutputDate >='" + 开始日期.ToString() + "'" +
                    " and b.OutputQualityGrade ='A2'";
            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = "update a set a.产出B =b.OutputWeight/1000 from   Temp_投入产出表 a ,Bus_SiOutput b ";
            sql += "where a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "' and b.OutputDate >='" + 开始日期.ToString() + "'" +
                  " and b.OutputQualityGrade ='B'";
            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = "update a set a.产出s =b.OutputWeight/1000 from   Temp_投入产出表 a ,Bus_SiOutput b ";
            sql += "where a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "' and b.OutputDate >='" + 开始日期.ToString() + "'" +
                   " and b.OutputQualityGrade ='S'";
            pub.ExecuteSql(pub.ConnectionString, sql);

            sql = "update a set a.汇总面积 =CONVERT(decimal(18, 0),b.OutputWidth)*CONVERT(decimal(18, 0),b.OutputMeter)/1000 from   Temp_投入产出表 a ,Bus_SiOutput b";
            sql += " where a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "' and b.OutputDate >='" + 开始日期.ToString() + "'";
            pub.ExecuteSql(pub.ConnectionString, sql);

            //6塞入罩退的
            sql = "insert into Temp_投入产出表(guid, 产线名称, 产出日期, 投入卷号, 产出卷号,   产出总重量  )";
            sql += "SELECT    '" + guid + "',产线名称,生产日期,[投入卷号],产出卷号,产出重量 FROM      View_罩退投入产出表 ";
            sql += " where 生产日期 >='" + 开始日期.ToString() + "'";

            pub.ExecuteSql(pub.ConnectionString, sql);

            sql = "update a set a.投入重量 =b.OriginalWeight/1000 from   Temp_投入产出表 a ,Bus_CoverFireOriginal b ";
            sql += "where a.投入卷号=b.OriginalVolumeNew and a.guid='" + guid + "'  and b.CreateDate >='" + 开始日期.ToString() + "' ";

            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = "update a set a.产出A1 =b.OutputWeight/1000 from   Temp_投入产出表 a ,Bus_CoverFireOutput b";
            sql += " where a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "' and b.OutputDate >='" + 开始日期.ToString() + "'" +
                " and b.OutputQualityGrade ='A1'";
            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = "update a set a.产出A2 =b.OutputWeight/1000 from   Temp_投入产出表 a ,Bus_CoverFireOutput b";
            sql += " where a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "' and b.OutputDate >='" + 开始日期.ToString() + "'" +
                    " and b.OutputQualityGrade ='A2'";
            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = "update a set a.产出B =b.OutputWeight/1000 from   Temp_投入产出表 a ,Bus_CoverFireOutput b ";
            sql += " where a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "' and b.OutputDate >='" + 开始日期.ToString() + "'" +
                  " and b.OutputQualityGrade ='B'";
            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = "update a set a.产出s =b.OutputWeight/1000 from   Temp_投入产出表 a ,Bus_CoverFireOutput b ";
            sql += " where a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "' and b.OutputDate >='" + 开始日期.ToString() + "'" +
                   " and b.OutputQualityGrade ='S'";
            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = "update a set a.汇总面积 =CONVERT(decimal(18, 0),b.OutputWidth)*CONVERT(decimal(18, 0),b.OutputMeter)/1000 from   Temp_投入产出表 a ,Bus_CoverFireOutput b ";
            sql += " where a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "' and b.OutputDate >='" + 开始日期.ToString() + "'";
            pub.ExecuteSql(pub.ConnectionString, sql);

            ///开始分摊
            sql = "  update a set a.产出汇总重量=b.产出汇总重量   from [Temp_投入产出表] a ,(select 投入卷号 ,sum(产出总重量) 产出汇总重量 ";
            sql += " from [Temp_投入产出表] where guid='" + guid + "' group by 投入卷号 ) b  where a.投入卷号=b.投入卷号 and a.guid='" + guid + "' ";
            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = "     update [Temp_投入产出表] set  分摊重量=投入重量*产出总重量/产出汇总重量 where guid='" + guid + "'  ";
            pub.ExecuteSql(pub.ConnectionString, sql);

        }
        private DataTable query(string guid)
        {
            string sql = @"SELECT  Dic_WorkLine.V3,Dic_WorkLine.V1 生产线, sum([分摊重量]) 投入量,sum(isnull(产出A1,0)) A1,sum(isnull(产出A2,0)) A2,sum(isnull(产出B,0)) B,sum(isnull(产出S,0)) S,
                            sum(isnull(产出A1,0)) + sum(isnull(产出A2,0)) + sum(isnull(产出B,0)) 总产出
                            ,(sum(isnull(产出A1,0)) + sum(isnull(产出A2,0))+sum(isnull(产出B,0)))/ sum(分摊重量)  成材率,(sum(isnull(产出B,0)) / (sum(isnull(产出A1,0)) + sum(isnull(产出A2,0))+sum(isnull(产出B,0))))  B级品率
                            ,sum(isnull(产出S,0))/ sum([分摊重量]) 废品率,sum(isnull(汇总面积,0)) 总面积 FROM [dbo].[Temp_投入产出表] ,Dic_WorkLine 
                            WHERE 1=1 and Dic_WorkLine.WorkLineName=Temp_投入产出表.产线名称 and Temp_投入产出表.guid='" + guid + "'";
            sql += " and [产出日期] >= '" + this.uiDatePicker开始日期.Value + "'";
            sql += " and [产出日期] < '" + this.uiDatePicker结束日期.Value.AddDays(1) + "'";
            sql += " group by V1,V3 order by V3";
            DataTable dataTable = pub.GetDataTable(pub.ConnectionString, sql);
            tbbb = dataTable;
            uiDataGridView临时.DataSource = dataTable;
            uiDataGridView临时.Refresh();
            sql = "delete Temp_投入产出表 where guid='" + guid + "'";
            pub.ExecuteSql(pub.ConnectionString, sql);
            return dataTable;
        }

        private void Form投入产出汇总_Load(object sender, EventArgs e)
        {
            this.uiDatePicker开始日期.Value = DateTime.Today.AddDays(-1);
            this.uiDatePicker结束日期.Value = DateTime.Today.AddDays(-1);
        }

        private void uiButton导出_Click(object sender, EventArgs e)
        {
            string path;

            saveFileDialog1.Filter = "Excel文件 (*.XLSX)|*.XLSX";
            if (this.saveFileDialog1.ShowDialog(this) == DialogResult.OK)
            {
                path = this.saveFileDialog1.FileName;
                if (!File.Exists(path))
                {
                    MiniExcel.SaveAs(path, tbbb, true, "sheet1", ExcelType.XLSX);
                }
                else
                {
                    MiniExcel.SaveAs(path, tbbb, true, "sheet1", ExcelType.XLSX, null, true);
                    return;
                }

            }
        }
    }
}
